## 7.4. ODBC from Perl

So we've established that the ODBC standard is a rather good thing, but how can you use it?

To use ODBC from Perl, there are only two practical options: the `Win32::ODBC` module and the DBI with the `DBD::ODBC` module. We'll describe `DBD::ODBC` first and then take a deeper look at `Win32::ODBC`.

### 7.4.1. DBD::ODBC

The `DBD::ODBC` module was written by Tim Bunce and Jeff Urlwin, based on original code by Thomas K. Wenrich. It's a Perl extension written in C and is not tied to Microsoft Win32 platforms. That makes it a good option for directly using ODBC on Unix, VMS, and other non-Windows systems.

Being a DBI driver, the main goal of the `DBD::ODBC` module is to implement the functionality required by the DBI, not simply to give access to ODBC from Perl.

The `DBD::ODBC` driver is described in more detail in Appendix B, "Driver and Database Characteristics ".

### 7.4.2. Win32::ODBC

The `Win32::ODBC` module was written by Dave Roth, based on original code by Dan DeMaggio. It's a Perl extension written in C++ and is closely associated with the Win32 platform.

The main goal of the `Win32::ODBC` module is to provide direct access to the ODBC functions. From that point of view, `Win32::ODBC` provides a fairly thin, low-level interface.

Here's a sample of `Win32::ODBC` code:

>     use Win32::ODBC;

>     ### Connect to a data source
>     $db = new Win32::ODBC("DSN=MyDataDSN;UID=me;PWD=secret")
>         or die Win32::ODBC::Error();

>     ### Prepare and Execute a statement
>     if ($db->Sql("SELECT item, price FROM table")) {
>         print "SQL Error: " . $db->Error() . "\n";
>         $db->Close();
>         exit;
>     }

>     ### Fetch row from data source
>     while ($db->FetchRow) {
>         my ($item, $price) = $db->Data();  ### Get data values from the row
>         print "Item $item = $price\n";
>     }

>     ### Disconnect
>     $db->Close();

The most significant disadvantages of `Win32::ODBC` compared to `DBD::ODBC` are:

There is no separate statement handle

  The database connection handle is used to store the details of the current statement. There is no separate statement handle, so only one statement can execute per database handle. But that's not as bad as it may seem, because it's possible to clone database handles so that more than one handle can share the same underlying ODBC database connection.

There are no separate prepare and execute steps

  You cannot prepare a statement for execution later. The `Sql()` method, like the DBI `do()` method, combines both.

Placeholders and bind parameters are not supported

  This is perhaps the most significant disadvantage of `Win32::ODBC`. All values must be passed as literal text values within the SQL statements.

  The lack of support for placeholders, especially when coupled with the inability to prepare statements, means that nontrivial applications based on `Win32::ODBC` tend to place a greater burden on database servers and thus run more slowly.

  It also causes problems when trying to insert binary data such as images.

Fetching rows is a two-step process

  The `FetchRow()` method doesn't actually return any data to the script. To get the row of data values, you need to call either the `Data()` method to get a simple list (like `fetchrow_array()` ), or the `DataHash()` method to get a hash (like `fetchrow_hashref()` ).

  This is more of a nuisance than a significant disadvantage. It's also another reason why `Win32::ODBC` is a little slower than using DBI.

There is no automatic error handling

  In ODBC, there is no equivalent to the DBI's `RaiseError` and `PrintError` mechanism. You need to explicitly test the return status of all `Win32::ODBC` method calls if you want to write a robust application.

  The lack of automatic error handling makes `Win32::ODBC` less suitable for nontrivial applications when application reliability is important. This is especially true where transactions are being used.

Win32::ODBC is slightly slower than DBD::ODBC

  Even for simple queries, `Win32::ODBC` tends to be slightly slower than `DBD::ODBC` for the same platform and database. As always with benchmarks, your mileage may vary, so test it yourself if this is an issue for you.

There are plans to address some of these disadvantages in a later release. The most significant advantages of `Win32::ODBC` compared to `DBD::ODBC` are:

Most of the ODBC API is available to use

  This is currently the biggest advantage that `Win32::ODBC` has over `DBD::ODBC`.

  The remaining items in this list are really significant ODBC features rather than features of the `Win32::ODBC` module itself, but until `DBD::ODBC` supports them, they still count as advantages of `Win32::ODBC`.

Attributes, options, and metadata are available

  These are described in the previous section. A wide range of metadata functions is available, along with functions for controlling many attributes and options.

Scrolling cursors are supported

  Scrolling cursors let you read the rows of data returned by a query in any order. You can jump to the last row and read backwards. You can jump to any row either by absolute row number or relative to the current row. That's very handy for interactive browsing applications.

Copyright © 2001 O'Reilly & Associates. All rights reserved.